<?php
/**
 * MVC_SQL
 * @Version 3.0
 * Upgrade from DB Class
 * date: 07May2008
 * 
 * @author Thanh NV
 * sony_k48@yahoo.com
 * 
 * @desc Feature
 * @desc provide basic functions for interact with Database
 * @desc Security MOD 
*/

class MVC_SQL extends MVC_ORM {
	/**
	 * @var String table name
	 */
	private $sTable = '';
	/**
	 * @var Array list fields of table
	 */
	private $aFields = array();
	/**
	 * @var String Primary Key
	 */
	private $sPrimaryKey = '';
	/**
	 * @var Array Foreign Keys
	 */
	private $aForeignKey = array();
	/**
	 * @var string Encrypt Type
	 */
	private $sEncryptType = 'md5';
	/**
	 * @var Array Encrypt Fields
	 * @desc Ex: array(array('Field'=>'table_id'));
	 */
	private $aEncryptField = array();
	/**
	 * @var unknown_type
	 */
	private $value_id = '';
	/**
	 * @var num rows 
	 */
	private $iNumRow = 0;
	
	
	/**
	 * __construct
	 *
	 * @param String $input_table
	 */
	public function __construct(){
		parent::db_connect();
	}
	
	public function setTable($sTable){
		parent::db_connect();
		
		$this->sTable = $sTable;
		$this->findListFields();
		$this->findPrivaryKey();
		$this->findForeignKey();
		
	}
	
	
	/**
	 * get All Field
	 */
	public function findListFields(){
		$rs = parent::db_query("SHOW COLUMNS FROM ".$this->sTable);
		while ($row = parent::db_fetch_array($rs)){
			$this->aFields[] = $row;
		}
		return true;
	}
	public function getListFields(){
		return $this->aFields;
	}
	
	private function isTableField($sField){
		foreach ($this->aFields as $row){
			if($sField == $row['Field']){
				return true;
			}
		}
		return false;
	}
	
	
	/**
	 * Primary Key functions
	 *
	 */
	public function setPrivaryKey($sPrimaryKey){
		$this->sPrimaryKey = $sPrimaryKey;
	}
	public function findPrivaryKey(){
		foreach ($this->aFields as $row){
			if($row['Key'] == "PRI"){
				$this->sPrimaryKey = $row['Field'];
				return true;
			}
		}
		return false;
	}
	public function getPrimaryKey(){
		return $this->sPrimaryKey;
	}
	
	private function isPrimaryKey($sPK){
		return ($sPK == $this->sPrimaryKey) ? true : false;
	}
	
	/**
	 * Foreign Key functions
	 *
	 */
	public function setForeignKey($aForeignKey){
		$this->aForeignKey = $aForeignKey;
	}
	public function findForeignKey(){
		$this->aForeignKey = array();
		
		foreach ($this->aFields as $row){
			if($row['Key'] == "MUL"){
				$this->aForeignKey[]=$row['Field'];
			}
		}
		
		return !$this->noForeignKey() ? true : false;
	}
	public function getForeignKey(){
		return $this->aForeignKey;
	}
	private function noForeignKey(){
		return (count($this->aForeignKey) == 0) ? true : false;
	}
	private function isForeignKey($sFK){
		if ($this->noForeignKey()){
			return false;
		}else{
			if (!in_array($sFK,$this->aForeignKey)) {
				return false;
			}
		}
		return true;
	}
	
	
	
	/**
	 * SELECT
	 * 
	 * get record set functions
	 *
	 */
	
	public function getNumRow($sSql){
		$rs = parent::db_query($sSql);
		return parent::db_num_rows($rs);
	}
	/**
	 * GET RECORDSET
	 *
	 */
	public function getRecordsetExtend($sSql){
		
		$aData = array();
		
		$rs = parent::db_query($sSql);
		
		if(!$rs || parent::db_num_rows($rs) == 0){
			
			return false;
		}else{
			while ($row = parent::db_fetch_array($rs)) {
				$row = parent::db_prepare_output($row);
				$aData[] = $row;
			}
			return $aData;
		}
	}
	
	public function getRecordset($sSqlWhere = ''){
		//$sSqlWhere = parent::db_prepare_input($sSqlWhere);
		
		$aData = array();
		$rs = parent::db_query("SELECT * FROM {$this->sTable} {$sSqlWhere}");
		if(!$rs || parent::db_num_rows($rs) == 0){
			return false;
		}else{
			while ($row = parent::db_fetch_array($rs)) {
				$row = parent::db_prepare_output($row);
				$aData[] = $row;
			}
			return $aData;
		}
	}
	
	
	/**
	 * GET ONE RECORD
	 *
	 */
	public function getRecordsetByPK($iPK){
		$iPK = parent::db_prepare_input($iPK);
		$rs = parent::db_query("SELECT * FROM {$this->sTable} WHERE {$this->sPrimaryKey}='{$iPK}'");
		if(!$rs || parent::db_num_rows($rs) == 0){		
			return false;
		}else{
			$row = parent::db_fetch_array($rs);
			$row = parent::db_prepare_output($row);
			return $row;
		}
	}
        /*
	public function getRecordsetByPKD($iPK){
		$iPK = parent::db_prepare_input($iPK);
		
		$rs = parent::db_query("SELECT * FROM ams_product_defaul WHERE product_id='1'");
		if(!$rs || parent::db_num_rows($rs) == 0){		
			return false;
		}else{
			$row = parent::db_fetch_array($rs);
			$row = parent::db_prepare_output($row);
			return $row;
		}
	}
         * 
         */
	public function getRecordsetByPKExtend($sSql){
		$rs = parent::db_query($sSql);
		if(!$rs || parent::db_num_rows($rs) == 0){
			return false;
		}else{
			$row = parent::db_fetch_array($rs);
			$row = parent::db_prepare_output($row);
			return $row;
		}
	}
	
	public function getRecordsetByFK($aFK){
		$aFK = parent::db_prepare_input($aFK);
		
		$sSqlWhere = " WHERE 1 ";
		if(is_array($aFK)){
			foreach ($aFK as $FK_name => $FK_value){
				if($this->isForeignKey($FK_name)){
					$sSqlWhere .= " AND {$FK_name} = '{$FK_value}' ";
				}
			}
		}
		$rs = parent::db_query("SELECT * FROM {$this->sTable} {$sSqlWhere}");
		
		if(!$rs || parent::db_num_rows($rs) == 0){		
			return false;
		}else{
			$row = parent::db_fetch_array($rs);
			$row = parent::db_prepare_output($row);
			return $row;
		}
	}
	
	public function getRecordsetByOtherField($aOtherField){
		$aOtherField = parent::db_prepare_input($aOtherField);
		
		$sSqlWhere = " WHERE 1 ";
		if(is_array($aOtherField)){
			foreach ($aOtherField as $sFieldName => $sFieldValue){
				if($this->isTableField($sFieldName)){
					$sSqlWhere .= " AND {$sFieldName} = '{$sFieldValue}' ";
				}
			}
		}
		$rs = parent::db_query("SELECT * FROM {$this->sTable} {$sSqlWhere}");
		
		if(!$rs || parent::db_num_rows($rs) == 0){		
			return false;
		}else{
			$row = parent::db_fetch_array($rs);
			$row = parent::db_prepare_output($row);
			return $row;
		}
	}
	
	
	
	
	/**
	 * INSERT
	 * 
	 * update database functions
	 *
	 */
	public function doInsert($aData, $bInsertForPK=false){
		$aData = parent::db_prepare_input($aData);
		
		$sSql = "INSERT INTO {$this->sTable} SET ";
		foreach ($this->aFields as $row){
			if($bInsertForPK || !$this->isPrimaryKey($row['Field'])){
				$sSql .= "{$row['Field']} = '{$aData[$row['Field']]}',";
			}
		}
		$sSql = substr($sSql,0,strlen($sSql)-1);
		$result = parent::db_query($sSql);
		
		if(!$result)
			return false;
		else 
			return parent::db_insert_id();
	}
	
	
	public function doCopyByPK ($iPK){
		$this->doInsert($this->getRecordsetByPK($iPK),false);
	}
	
	/**
	 * UPDATE
	 *
	 */
	public function doUpdateByPK($aData){
		$aData = parent::db_prepare_input($aData);
		
		$sSql = "UPDATE {$this->sTable} SET ";
		$sSqlWhere = "";
		foreach ($this->aFields as $row){
			if(!$this->isPrimaryKey($row['Field'])){
				if(isset($aData[$row['Field']])){
					$sSql .= "{$row['Field']} = '{$aData[$row['Field']]}',";
				}
			}else{
				$sSqlWhere = " WHERE {$row['Field']} = '{$aData[$row['Field']]}'";
			}
		}
		$sSql = substr($sSql,0,strlen($sSql)-1);
		$sSql .= $sSqlWhere;
		 
		$result = parent::db_query($sSql);
		return $result;
	}
	public function doUpdateByPK_OBJ($aData){
		$aData = parent::db_prepare_input($aData);
		
		$sSql = "UPDATE {$this->sTable} SET ";
		$sSqlWhere = "";
		foreach ($this->aFields as $row){
			if(!$this->isPrimaryKey($row['Field'])){
				if(isset($aData[$row['Field']])){
					$sSql .= "{$row['Field']} = '{$aData[$row['Field']]}',";
				}
			}else{
				$sSqlWhere = " WHERE {$row['Field']} = '{$aData[$row['Field']]}'";
			}
		}
		$sSql = substr($sSql,0,strlen($sSql)-1);
		$sSql .= $sSqlWhere;
		 //redir('httl://www.google.com'.$sSql);
		$result = parent::db_query($sSql);
		//return $result;
		return $sSql;
	}
	
	public function doUpdateByFK($aData){
		$aData = parent::db_prepare_input($aData);
		
		$sSql = "UPDATE {$this->sTable} SET ";
		$sSqlWhere = " WHERE 1 ";
		$bFoundOneFK = false;
		foreach ($this->aFields as $row){
			if(!$this->isPrimaryKey($row['Field'])){
				if(isset($aData[$row['Field']])){
					$sSql .= "{$row['Field']} = '{$aData[$row['Field']]}',";
				}
			}elseif ($this->isForeignKey($row['Field']) && isset($aData[$row['Field']])){
				$bFoundOneFK = true;
				$sSqlWhere = " AND {$row['Field']} = '{$aData[$row['Field']]}'";
			}
		}
		$sSql = substr($sSql,0,strlen($sSql)-1);
		$sSql .= $sSqlWhere;
		
		if(!$bFoundOneFK){
			return false;
		}
		
		$result = parent::db_query($sSql);
		return $result;
	}
	
	
	public function doUpdateByCondition($sSqlWhere){
		//$sSqlWhere = parent::db_prepare_input($sSqlWhere);
		
		$sSql = "UPDATE {$this->sTable} SET ";
		foreach ($this->aFields as $row){
			if(!$this->isPrimaryKey($row['Field'])){
				if(isset($aData[$row['Field']])){
					$sSql .= "{$row['Field']} = '{$aData[$row['Field']]}',";
				}
			}
		}
		$sSql = substr($sSql,0,strlen($sSql)-1);
		$sSql .= $sSqlWhere;
		
		$result = parent::db_query($sSql);
		return $result;
	}
	
	public function doUpdateExtend($sSql){
		$result=parent::db_query($sSql);
		return $result;
	}
	/**
	 * DELETE
	 *
	 */
	
	public function doDeleteByPK($iPK){
		return parent::db_query("DELETE FROM {$this->sTable} WHERE {$this->sPrimaryKey}='{$iPK}'");
	}
	public function doDeleteByFK($aFK){
		$aData = parent::db_prepare_input($aData);
		
		$sSql = "DELETE FROM {$this->sTable} ";
		$sSqlWhere = " WHERE 1 ";
		$bFoundOneFK = false;
		foreach ($this->aFields as $row){
			if ($this->isForeignKey($row['Field']) && isset($aData[$row['Field']])){
				$bFoundOneFK = true;
				$sSqlWhere = " AND {$row['Field']} = '{$aData[$row['Field']]}'";
			}
		}
		$sSql .= $sSqlWhere;
		
		if(!$bFoundOneFK){
			return false;
		}
		
		$result = parent::db_query($sSql);
		return $result;
	}
	public function doDeleteByCondition($sSqlWhere){
		return parent::db_query("DELETE FROM {$this->sTable} {$sSqlWhere}'");
	}
	public function doDeleteExtend($sSql){
		$result=parent::db_query($sSql);

		return $result;

	}

}
?>